import sqlite3
import codecs

conn = sqlite3.connect('../instance/database.bin')

cursor = conn.cursor()

SQL_code: str
with codecs.open('./schema.sql', mode='r', encoding='UTF-8') as file:
    SQL_code = file.read()
    cursor.executescript(SQL_code)

SQL_code = '''
    SELECT c.numberID,c.authorID, c.prevCommentID,
        u.nickname,u1.nickname AS parent_nickname
    FROM comment AS c, user AS u, comment AS c1, user AS u1
    WHERE c.authorID = u.numberID
        AND c.prevCommentID=c1.numberID
        AND c1.authorID = u1.numberID
        AND c.articleID=1;
'''
print(type(conn))

print('-------user表格-------')
cursor.execute(SQL_code)
for tmp in cursor.description:
    print(f"{tmp[0]}", end=' ')
result = cursor.fetchall()
for row in result:
    print(row)
print('数据条数:', len(result))

print('--------user+article表格---------')
SQL_code = '''
SELECT *
FROM article AS a
INNER JOIN user AS u
    ON a.authorID = u.numberID
ORDER BY u.numberID;
'''
cursor.execute(SQL_code)
for tmp in cursor.description:
    print(f"{tmp[0]}", end=' ')
print()
for row in cursor.fetchall():
    print(row)

print('-------点赞表格-------')
SQL_code = '''
SELECT *
FROM like_dislike;
'''
cursor.execute(SQL_code)
print('数据总数:', len(cursor.fetchall()))


print('--------查询各个文章点赞数量、作者名称--------')
SQL_code = '''
SELECT a.numberID, SUM(l.direction) as num_likes,
    a.title, a.body,
    a.authorID, a.last_modified_time, 
    u.nickname
FROM article AS a, user AS u
LEFT OUTER JOIN like_dislike AS l
    ON l.articleID=a.numberID
WHERE a.authorID=u.numberID
GROUP BY a.numberID
ORDER BY a.numberID;
'''


cursor.execute(SQL_code)
result = cursor.fetchall()
print('数据长度:', len(result))

for tmp in cursor.description:
    print(f"{tmp[0]}", end=' ')
print()
for row in result:
    print(row)
conn.close()
